const pool = require('../config/database');

// 获取所有药品
const getAllDrugs = async () => {
    try {
        const [rows] = await pool.execute('SELECT * FROM drugs');
        return rows;
    } catch (error) {
        throw error;
    }
};

// 创建新药品
const createDrug = async (name, description, price, stock, image, category) => {
    try {
        const [result] = await pool.execute(
            'INSERT INTO drugs (name, description, price, stock, image, category) VALUES (?, ?, ?, ?, ?, ?)',
            [name, description, price, stock, image, category]
        );
        return result.insertId;
    } catch (error) {
        throw error;
    }
};

// 根据分类获取药品
const getDrugsByCategory = async (category) => {
    try {
        const [rows] = await pool.execute(
            'SELECT * FROM drugs WHERE category = ?',
            [category]
        );
        return rows;
    } catch (error) {
        throw error;
    }
};

// 根据 ID 获取药品
const getDrugById = async (id) => {
    try {
        const [rows] = await pool.execute('SELECT * FROM drugs WHERE id = ?', [id]);
        return rows[0];
    } catch (error) {
        throw error;
    }
};

// 更新药品信息
const updateDrug = async (id, name, description, price, stock, image, category) => {
    try {
        const [result] = await pool.execute(
            'UPDATE drugs SET name = ?, description = ?, price = ?, stock = ?, image = ?, category = ? WHERE id = ?',
            [name, description, price, stock, image, category, id]
        );
        return result.affectedRows;
    } catch (error) {
        throw error;
    }
};

// 更新药品库存
const updateDrugStock = async (id, quantity) => {
    try {
        const [result] = await pool.execute(
            'UPDATE drugs SET stock = stock - ? WHERE id = ? AND stock >= ?',
            [quantity, id, quantity]
        );
        return result.affectedRows;
    } catch (error) {
        throw error;
    }
};

// 删除药品
const deleteDrug = async (id) => {
    try {
        const [result] = await pool.execute('DELETE FROM drugs WHERE id = ?', [id]);
        return result.affectedRows;
    } catch (error) {
        throw error;
    }
};

module.exports = {
    getAllDrugs,
    createDrug,
    getDrugsByCategory,
    getDrugById,
    updateDrug,
    updateDrugStock,
    deleteDrug
};